const express = require('express');
const router = express.Router();
const connection = require("../util/db")
const ResultJson = require("../util/json")
const {formatArray, formatObject} = require("../util/jsonFormat")

router.get("/", function (req, res) {
    res.render("userinfo/userinfo-list")
})
router.get("/page", function (req, res) {
    let {page = 1, limit = 5, username, nickname, status} = req.query;
    let sql = "select u.*,p.power_name from userinfo u left join power p on u.power_id = p.id where 1 = 1 ";
    const objs = [];
    if (username) {
        sql += "and u.username = ? ";
        objs.push(username);
    }
    if (nickname) {
        sql += "and u.nickname like ? ";
        objs.push(`%${nickname}%`);
    }
    if (status) {
        sql += "and status = ? ";
        objs.push(status);
    }
    sql += "order by u.id desc limit ?,?";

    /*数据类型转换*/
    page = parseInt(page);
    limit = parseInt(limit);

    let offset = (page - 1) * limit;
    objs.push(offset, limit);

    let count = 0;
    /*执行分页查询*/
    connection.query(sql, objs, function (e, r) {
        if (e) {
            throw e;
        }
        const userinfoList = formatArray(r);
        if (userinfoList.length > 0) {
            const countObjs = [];
            let countSql = "select count(u.id) count from userinfo u left join power p on u.power_id = p.id where 1 = 1 ";
            if (username) {
                countSql += "and u.username = ? ";
                countObjs.push(username);
            }
            if (nickname) {
                countSql += "and u.nickname like ? ";
                countObjs.push(`%${nickname}%`);
            }
            if (status) {
                countSql += "and status = ? ";
                countObjs.push(status);
            }
            connection.query(countSql, countObjs, function (e, r) {
                if (e) {
                    throw e;
                }
                count = r[0].count;
                res.json(ResultJson.page(count, userinfoList));
            })
        } else {
            res.json(ResultJson.page(count, userinfoList));
        }
    })
})
router.delete("/delete/:id", function (req, res) {
    const id = parseInt(req.params.id);
    let sql = "delete from userinfo where id = ?";
    connection.query(sql, id, function (e, r) {
        if (e) {
            throw e;
        }
        res.json(ResultJson.ok());
    })
})
router.get("/edit", function (req, res) {
    let id = req.query.id;
    let userinfo = {};
    if (id) {
        id = parseInt(id);
        let sql = "select * from userinfo where id = ?";
        connection.query(sql, id, function (e, r) {
            if (e) {
                throw e;
            }
            if (r.length === 1) {
                userinfo = r[0];
            }
            userinfo = formatObject(userinfo);
            res.render("userinfo/userinfo-update", {userinfo})
        })
    } else {
        res.render("userinfo/userinfo-update", {userinfo})
    }
})

router.post("/update", function (req, res) {
    const {id, username, nickname, status, powerId, avatar, image} = req.body;
    if (id) {
        let sql = "update userinfo set nickname = ?,status = ?,power_id = ?,avatar = ? where id = ?";
        connection.query(sql, [nickname, status, powerId, avatar, id], function (e) {
            if (e) {
                throw e
            }
            /*修改完成后先删除所有的关联图片*/
            connection.query("delete from image where user_id = ?", id, function (e) {
                if (e) {
                    throw e;
                }
                /*批量添加图片*/
                batchAddImage(image, id, res);
            })
        })
    } else {
        let sql = "select * from userinfo where username = ?";
        connection.query(sql, username, function (e, r) {
            if (e) {
                throw e;
            }
            if (r.length > 0) {
                res.json(ResultJson.error("用户名已存在！"))
            } else {
                sql = "insert into userinfo values(null,?,'123456',?,?,?,?)";
                connection.query(sql, [username, nickname, status, powerId, avatar], function (e) {
                    if (e) {
                        throw e;
                    }
                    connection.query("select LAST_INSERT_ID() lastId", [], function (e, r) {
                        const lastId = r[0].lastId;//获取添加成功后的数据编号
                        batchAddImage(image, lastId, res);
                    })
                })
            }
        })
    }
})

router.get("/password", function (req, res) {
    res.render("userinfo/userinfo-password")
})
router.post("/password", function (req, res) {
    const {oldPwd, password, id} = req.body;
    /*验证原密码*/
    let sql = "select * from userinfo where id = ? and password = ?";
    connection.query(sql, [id, oldPwd], function (e, r) {
        if (e) {
            throw e;
        }
        if (r.length === 1) {
            sql = "update userinfo set password = ? where id = ?";
            connection.query(sql, [password, id], function (e) {
                if (e) {
                    throw e;
                }
                res.json(ResultJson.ok());
            })
        } else {
            res.json(ResultJson.error("原密码不正确！"));
        }
    })
})

router.post("/status", function (req, res) {
    const {status, id} = req.body;
    let sql = "update userinfo set status = ? where id = ?";
    connection.query(sql, [status, id], function (e) {
        if (e) {
            throw e;
        }
        res.json(ResultJson.ok());
    })
})

const batchAddImage = (image, id, res) => {
    if (image) {
        const images = image.split(",");
        /*遍历图片数组拼接批量添加的SQL*/
        let imageSql = "insert into image values";
        let imageArray = [];
        for (let i of images) {
            imageSql += '(null,?,?),';
            imageArray.push(i, id);
        }
        imageSql = imageSql.substring(0, imageSql.length - 1);
        connection.query(imageSql, imageArray, function (e) {
            if (e) {
                throw e;
            }
            res.json(ResultJson.ok());
        })
    } else {
        res.json(ResultJson.ok());
    }
}
module.exports = router;
